package hotel.discard;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import hotel.common.JDBC;
import hotel.modal.CustomerType;

public class CustomerTypeSql {
	protected static Connection conn = null;
	protected static Statement stmt = null;
	protected static ResultSet rs = null;
	
	// 获取所有会员类型信息
	public static ArrayList<CustomerType> getAll() {
		String sql;
		ArrayList<CustomerType> data = new ArrayList<CustomerType>();
		try {
			conn = JDBC.getConnection();
			if (conn != null) {
				stmt = conn.createStatement();
				sql = "SELECT CustomerTypeID, TypeName, DiscountRate FROM customer_type order by CustomerTypeID";
				// System.out.println(sql);
				rs = stmt.executeQuery(sql);
				String typeName;
				int customerTypeId;
				double discountRate;
				CustomerType customerType = null;
				while (rs.next()) {
					typeName = rs.getString("TypeName").trim();
					customerTypeId = rs.getInt("CustomerTypeID");
					discountRate = rs.getDouble("DiscountRate");
					customerType = new CustomerType(customerTypeId, typeName, discountRate);
					data.add(customerType);
				}
				stmt.close();
				/*
				System.out.println("数据库表Student已有数据" + data.size() + "条");
				for (int i = 0; i < data.size(); i++) {
					System.out.println(data.get(i).toString()); }*/
				 
			}
			JDBC.closeConnection();
		} catch (SQLException se) {
			System.out.println("Error Code: " + se.getErrorCode() + " Message: " + se.getMessage());
		} finally {
			CustomerTypeSql.closeAll();
		}

		return data;
	}
	
	// 添加会员类型信息
	public static Boolean add(CustomerType customerType) {
		String sql;
		int ret = 0;
		if(customerType.getCustomerTypeId() == -1) return false;
		try {
			conn = JDBC.getConnection();
			if (conn == null) {
				return false;
			}
			stmt = conn.createStatement();
			sql = String.format("insert into customer_type(CustomerTypeID, TypeName, DiscountRate) "
							+"values(%d,'%s',%.2f)",customerType.getCustomerTypeId(),
							customerType.getTypeName(), customerType.getDiscountRate());
			//System.out.println(sql);
			ret = stmt.executeUpdate(sql);
			stmt.close();
			JDBC.closeConnection();
		} catch (SQLException se) {
			System.out.println("Error Code: " + se.getErrorCode() + " Message: " + se.getMessage());
		} finally {
			CustomerTypeSql.closeAll();
		}
		if (ret == 1)
			return true;
		else
			return false;
	}
	
	// 更新会员类型信息
	public static Boolean update(CustomerType customerType) {
		String sql;
		int ret = 0;
		try {
			conn = JDBC.getConnection();
			if (conn == null) {
				return false;
			}
			stmt = conn.createStatement();
			sql = String.format("update customer_type set TypeName='%s', "
							+"DiscountRatePrincipal=%0.2f where CustomerTypeID=%d",
					customerType.getTypeName(), customerType.getDiscountRate(), customerType.getCustomerTypeId());
			//System.out.println(sql);
			ret = stmt.executeUpdate(sql);
			stmt.close();
			JDBC.closeConnection();
		} catch (SQLException se) {
			System.out.println("Error Code: " + se.getErrorCode() + " Message: " + se.getMessage());
		} finally {
			CustomerTypeSql.closeAll();
		}
		if (ret == 1)
			return true;
		else
			return false;
	}
	
	// 删除会员类型信息
	public static Boolean delete(int customerTypeId) {
		String sql;
		int ret = 0;
		try {
			conn = JDBC.getConnection();
			if (conn == null) {
				return false;
			}
			stmt = conn.createStatement();
			sql = String.format("delete from customer_type where CustomerTypeID='%s'", customerTypeId);
			//System.out.println(sql);
			ret = stmt.executeUpdate(sql);
			stmt.close();
			JDBC.closeConnection();
		} catch (SQLException se) {
			System.out.println("Error Code: " + se.getErrorCode() + " Message: " + se.getMessage());
		}finally {
			CustomerTypeSql.closeAll();
		}
		if (ret == 1)
			return true;
		else
			return false;
	}
	
	
	private static void closeAll() {
		if (rs != null) try { rs.close(); } catch(Exception e) {}
		if (stmt != null) try { stmt.close(); } catch(Exception e) {}
		if (conn != null) try { conn.close(); } catch(Exception e) {}
	}
}
